package deng.qiya.dao;



import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import deng.qiya.model.TextMessage;
import deng.qiya.util.CustomerContextHolder;
import deng.qiya.wechat.Constant;


public class TextMessageOper {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	
	public TextMessage getTextMessageByUserName(String userName){
		String sql = "SELECT TOP 1 [ID]      ,[TO_USERNAME]      ,[FROM_USERNAME]      ,[CREATE_TIME]      ,[MESSAGE_TYPE]      ,[CONTENT]      ,[IS_REPLY]      ,[REPLY_TIME]      ,[SYSTEM_TIME]      ,[QUESTION_TYPE]  FROM [TEXT_MESSAGE] WHERE FROM_USERNAME=? ORDER BY SYSTEM_TIME DESC";
		CustomerContextHolder.setCustomerType(Constant.ds);
		Object[] params = new Object[]{userName};
		List<TextMessage> list = jdbcTemplate.query(sql, new TextMessageMapper(), params);
		//TextMessage message = jdbcTemplate.queryForObject(sql, new TextMessageMapper(),params);
		if(list !=null && list.size()>0){
			return list.get(0);
		}
		else {
			return null;
		}
	}
	
	public TextMessage getTextMessageByUserName(String userName,int execlueId){
		String sql = "SELECT TOP 1 [ID]      ,[TO_USERNAME]      ,[FROM_USERNAME]      ,[CREATE_TIME]      ,[MESSAGE_TYPE]      ,[CONTENT]      ,[IS_REPLY]      ,[REPLY_TIME]      ,[SYSTEM_TIME]      ,[QUESTION_TYPE]  FROM [TEXT_MESSAGE] WHERE FROM_USERNAME=? AND [ID]<>? ORDER BY SYSTEM_TIME DESC";
		CustomerContextHolder.setCustomerType(Constant.ds);
		Object[] params = new Object[]{userName,execlueId};
		List<TextMessage> list = jdbcTemplate.query(sql, new TextMessageMapper(), params);
		//TextMessage message = jdbcTemplate.queryForObject(sql, new TextMessageMapper(),params);
		if(list !=null && list.size()>0){
			return list.get(0);
		}
		else {
			return null;
		}
	}
	
	public TextMessage getTextMessageByUserName(String userName,String exculeId){
		String sql = "SELECT TOP 1 [ID]      ,[TO_USERNAME]      ,[FROM_USERNAME]      ,[CREATE_TIME]      ,[MESSAGE_TYPE]      ,[CONTENT]      ,[IS_REPLY]      ,[REPLY_TIME]      ,[SYSTEM_TIME]      ,[QUESTION_TYPE]  FROM [TEXT_MESSAGE] WHERE FROM_USERNAME=? AND ID<>? ORDER BY SYSTEM_TIME DESC";
		CustomerContextHolder.setCustomerType(Constant.ds);
		Object[] params = new Object[]{userName,exculeId};
		List<TextMessage> list = jdbcTemplate.query(sql, new TextMessageMapper(), params);
		//TextMessage message = jdbcTemplate.queryForObject(sql, new TextMessageMapper(),params);
		if(list !=null && list.size()>0){
			return list.get(0);
		}
		else {
			return null;
		}
	}
	
	public TextMessage getTextMessageByUserNameAndType(String userName,String questionType){
		String sql = "SELECT TOP 1 [ID]      ,[TO_USERNAME]      ,[FROM_USERNAME]      ,[CREATE_TIME]      ,[MESSAGE_TYPE]      ,[CONTENT]      ,[IS_REPLY]      ,[REPLY_TIME]      ,[SYSTEM_TIME]      ,[QUESTION_TYPE]  FROM [TEXT_MESSAGE] WHERE FROM_USERNAME=? AND QUESTION_TYPE=? ORDER BY SYSTEM_TIME DESC";
		CustomerContextHolder.setCustomerType(Constant.ds);
		Object[] params = new Object[]{userName,questionType};
		List<TextMessage> list = jdbcTemplate.query(sql, new TextMessageMapper(), params);
		//TextMessage message = jdbcTemplate.queryForObject(sql, new TextMessageMapper(),params);
		if(list !=null && list.size()>0){
			return list.get(0);
		}
		else {
			return null;
		}
	}

	
	public void updateTextMessage(int id, String questionType){
		String sql ="UPDATE TEXT_MESSAGE SET QUESTION_TYPE=? WHERE ID=?";
		CustomerContextHolder.setCustomerType(Constant.ds);
		jdbcTemplate.update(sql,new Object[]{questionType,id});
	}

	public int newTextMessage(final TextMessage textMessage){
		final String sqlString="INSERT INTO [TEXT_MESSAGE]([TO_USERNAME],[FROM_USERNAME],[CREATE_TIME],[MESSAGE_TYPE]," +
				"[CONTENT],[IS_REPLY],[REPLY_TIME],[SYSTEM_TIME],[QUESTION_TYPE]) VALUES(?,?,?,?,?,?,?,?,?)";
		CustomerContextHolder.setCustomerType(Constant.ds);
		//KeyHolder keyHolder = new GeneratedKeyHolder();
		
		jdbcTemplate.update(sqlString,new Object[]{textMessage.getToUserName(),
				textMessage.getFromUserName(),
				textMessage.getCreateTime(),
				textMessage.getMessageType(),
				textMessage.getContent(),
				textMessage.getIsReply(),
				textMessage.getReplyTime(),
				textMessage.getSystemTime(),
				textMessage.getQuestionType()
			});
		
		return jdbcTemplate.queryForInt( "select @@identity" );
//		jdbcTemplate.update(new PreparedStatementCreator() {
//	        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
//	            PreparedStatement ps =
//	                connection.prepareStatement(sqlString, new String[] {"id"});
//	           ps.setString(1, textMessage.getToUserName());
//	           ps.setString(2, textMessage.getFromUserName());
//	           ps.setString(3, textMessage.getCreateTime());
//	           ps.setString(4, textMessage.getMessageType());
//	           ps.setString(5, textMessage.getContent());
//	           ps.setString(6, textMessage.getIsReply());
//	           ps.setDate(7, new java.sql.Date(textMessage.getReplyTime().getTime()));
//	           ps.setDate(8, new java.sql.Date(textMessage.getSystemTime().getTime()));
//	           ps.setString(9, textMessage.getQuestionType());
//	            return ps;
//	        }
//	    }, keyHolder);
		
//		return keyHolder.getKey().intValue();
	}
	
	
}

class TextMessageMapper implements RowMapper<TextMessage>{

	@Override
	public TextMessage mapRow(ResultSet rs, int rowNum) throws SQLException {
			TextMessage textMessage = new TextMessage();
			textMessage.setContent(rs.getString("CONTENT"));
			textMessage.setCreateTime(rs.getString("TO_USERNAME"));
			textMessage.setFromUserName(rs.getString("FROM_USERNAME"));
			textMessage.setId(new Integer(rs.getString("ID")));
			textMessage.setIsReply(rs.getString("IS_REPLY"));
			textMessage.setMessageType(rs.getString("MESSAGE_TYPE"));
			textMessage.setQuestionType(rs.getString("QUESTION_TYPE"));
			textMessage.setReplyTime(rs.getDate("REPLY_TIME"));
			textMessage.setReplyTime(rs.getTimestamp("REPLY_TIME"));
			textMessage.setSystemTime(rs.getTimestamp("SYSTEM_TIME"));
			textMessage.setToUserName(rs.getString("TO_USERNAME"));
		return textMessage;
	}
	
}
